package clustering;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;

public class ClustersDataGenerator {
	static final boolean IS_TEST = false;

	final int QT1_START = 1, QT1_END = 6;
	final int QT2_START = 7, QT2_END = 12;
	final int QT3_START = 13, QT3_END = 18;
	final int QT4_START = 19, QT4_END = 0;

	final String dbString = "jdbc:mysql://localhost/cdr_db?user=root&password=";

	Connection connect = null;
	PreparedStatement pstmt = null;
	ResultSet resultSet = null;
	Calendar begin_time;
	Calendar end_time;

	public ClustersDataGenerator(Calendar begin_time, Calendar end_time) {
		this.begin_time = begin_time;
		this.end_time = end_time;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection(dbString);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public List<SubscriberCompactRecord> generateRecords() throws SQLException {
		List<String> allSubscribers = new ArrayList<String>();
		Statement statement = connect.createStatement();
		resultSet = statement.executeQuery("select user_id from users");

		int c_test = 0;
		while (resultSet.next()) {
			String subscriber_id = resultSet.getString("user_id");
			allSubscribers.add(subscriber_id);

			c_test++;
			if ((c_test == 100) && (IS_TEST))
				break;
		}
		List<SubscriberCompactRecord> scrs = new ArrayList<SubscriberCompactRecord>();
		pstmt = connect
				.prepareStatement("select  COUNT(*) as nbr_calls, AVG(duration) as avg_duration "
						+ " from calls where src_id = ? and FROM_UNIXTIME(timestamp) >= ? and FROM_UNIXTIME(timestamp) <= ? "
						+ "and HOUR(FROM_UNIXTIME(timestamp)) >= ? and HOUR(FROM_UNIXTIME(timestamp)) <= ?");
		Calendar time = Calendar.getInstance();
		time.set(Calendar.YEAR, begin_time.get(Calendar.YEAR));
		time.set(Calendar.MONTH, begin_time.get(Calendar.MONTH));
		time.set(Calendar.DATE, begin_time.get(Calendar.DATE));
		time.set(Calendar.HOUR_OF_DAY, begin_time.get(Calendar.HOUR_OF_DAY));
		time.set(Calendar.MINUTE, begin_time.get(Calendar.MINUTE));
		time.set(Calendar.SECOND, begin_time.get(Calendar.SECOND));
		pstmt.setTimestamp(2, new java.sql.Timestamp(time.getTime().getTime()));
		time.set(Calendar.YEAR, end_time.get(Calendar.YEAR));
		time.set(Calendar.MONTH, end_time.get(Calendar.MONTH));
		time.set(Calendar.DATE, end_time.get(Calendar.DATE));
		time.set(Calendar.HOUR_OF_DAY, end_time.get(Calendar.HOUR_OF_DAY));
		time.set(Calendar.MINUTE, end_time.get(Calendar.MINUTE));
		time.set(Calendar.SECOND, end_time.get(Calendar.SECOND));
		pstmt.setTimestamp(3, new java.sql.Timestamp(time.getTime().getTime()));
		for (String subscriber_id : allSubscribers) {
			SubscriberCompactRecord scr = new SubscriberCompactRecord();
			scr.subscriber_id = subscriber_id;
			pstmt.setString(1, subscriber_id);

			pstmt.setInt(4, QT1_START);
			pstmt.setInt(5, QT1_END);
			resultSet = pstmt.executeQuery();
			resultSet.next();
			scr.nbr_calls_qrt1 = resultSet.getInt("nbr_calls");
			scr.avg_call_duration_qrt1 = resultSet.getDouble("avg_duration");

			pstmt.setInt(4, QT2_START);
			pstmt.setInt(5, QT2_END);
			resultSet = pstmt.executeQuery();
			resultSet.next();
			scr.nbr_calls_qrt2 = resultSet.getInt("nbr_calls");
			scr.avg_call_duration_qrt2 = resultSet.getDouble("avg_duration");

			pstmt.setInt(4, QT3_START);
			pstmt.setInt(5, QT3_END);
			resultSet = pstmt.executeQuery();
			resultSet.next();
			scr.nbr_calls_qrt3 = resultSet.getInt("nbr_calls");
			scr.avg_call_duration_qrt3 = resultSet.getDouble("avg_duration");

			pstmt.setInt(4, QT4_START);
			pstmt.setInt(5, QT4_END);
			resultSet = pstmt.executeQuery();
			resultSet.next();
			scr.nbr_calls_qrt4 = resultSet.getInt("nbr_calls");
			scr.avg_call_duration_qrt4 = resultSet.getDouble("avg_duration");
			if (scr.totalNumberOfCalls() == 0) {
				System.out.println("No calls for " + subscriber_id);
			} else {
				System.out.println(scr);
				scrs.add(scr);
			}
		}
		return scrs;
	}

	private void exportToARFF(List<SubscriberCompactRecord> scrs) {
		String NL = System.getProperty("line.separator");
		StringBuilder sb = new StringBuilder();
		sb.append("% SCAMSTOP" + NL);
		sb.append("@RELATION call_data" + NL);
		// sb.append("@ATTRIBUTE subscriber_id STRING" + NL);
		sb.append("@ATTRIBUTE nbr_calls_qrt1 NUMERIC" + NL);
		sb.append("@ATTRIBUTE avg_call_duration_qrt1 NUMERIC" + NL);
		sb.append("@ATTRIBUTE nbr_calls_qrt2 NUMERIC" + NL);
		sb.append("@ATTRIBUTE avg_call_duration_qrt2 NUMERIC" + NL);
		sb.append("@ATTRIBUTE nbr_calls_qrt3 NUMERIC" + NL);
		sb.append("@ATTRIBUTE avg_call_duration_qrt3 NUMERIC" + NL);
		sb.append("@ATTRIBUTE nbr_calls_qrt4 NUMERIC" + NL);
		sb.append("@ATTRIBUTE avg_call_duration_qrt4 NUMERIC" + NL);
		sb.append("@DATA" + NL);
		for (SubscriberCompactRecord scr : scrs) {
			// sb.append(String.format(Locale.US, "%s,%d,%.2f\n",
			// scr.getSubscriber_id(),
			// scr.getNbr_calls_qrt1(), scr.getAvg_call_duration_qrt1()));
			sb.append(String.format(Locale.US,
					"%d,%.2f,%d,%.2f,%d,%.2f,%d,%.2f,\n",
					scr.getNbr_calls_qrt1(), scr.getAvg_call_duration_qrt1(),
					scr.getNbr_calls_qrt2(), scr.getAvg_call_duration_qrt2(),
					scr.getNbr_calls_qrt3(), scr.getAvg_call_duration_qrt3(),
					scr.getNbr_calls_qrt4(), scr.getAvg_call_duration_qrt4()));

		}
		try {
			Writer output = new BufferedWriter(new FileWriter("temp.arff"));
			output.write(sb.toString());
			output.close();
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}

	public void exportData() {
		try {
			exportToARFF(generateRecords());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
